package com.leap.meeting.dao;

import com.leap.meeting.util.JDBCUtil;

import java.lang.reflect.Field;
import java.sql.*;

import java.util.ArrayList;
import java.util.List;

public class BaseDAO {

    //增删改
    public int executeUpdate(String sql,Object... obj){
        try {
            //1.获取连接
            Connection connection = JDBCUtil.getConnection();

            //2.预编译SQL语句
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            //3.为占位符取值,执行SQL,接受返回结果
            if(obj != null && obj.length > 0){
                for (int i = 0; i < obj.length; i++) {
                    preparedStatement.setObject(i+1, obj[i]);
                }
            }
            int r = preparedStatement.executeUpdate();

            //4.释放资源
            connection.commit();
            preparedStatement.close();
            if(!connection.getAutoCommit()){
                JDBCUtil.release();
            }

            //5.返回结果
            return r;
        } catch (SQLException e) {
            JDBCUtil.rollback();
            throw new RuntimeException(e);
        }
    }

    //查
    public <T> List<T> executeQuery(Class<T> clazz,String sql,Object... obj) throws Exception {
        //1.获取连接
        Connection connection = JDBCUtil.getConnection();

        //2.预编译SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //3.为占位符取值,执行SQL,接受返回结果
        if(obj != null && obj.length > 0){
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
        }

        //4.执行SQL 并接受返回的结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //获取结果集中的元数据对象
        //包含了 列的数量,每个列的名称
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        List<T> list = new ArrayList<T>();
        //处理结果
        while (resultSet.next()) {
            T t = clazz.newInstance();
            //循环遍历当前行的每个列,循环多次,查看有多少列
            for (int i = 0; i < columnCount; i++) {
                Object value = resultSet.getObject(i + 1);

                //获取到列的value值,这个值就是t这个对象中的某一个属性
                //获取当前拿到的列的名字 = 对象的属性名
                String fileName = metaData.getColumnLabel(i+1);
                //通过类对象和fileName获取要封装的对象的属性
                Field field = clazz.getDeclaredField(fileName);
                field.setAccessible(true);
                field.set(t, value);
            }
            list.add(t);
        }
        connection.commit();
        resultSet.close();
        preparedStatement.close();
        if(!connection.getAutoCommit()){
            JDBCUtil.release();
        }

        return list;
    }

    //查询单个结果
    public <T> T executeQuerySingle(Class<T> clazz,String sql,Object... obj) throws Exception {
        List<T> list = executeQuery(clazz,sql,obj);
        if(list == null || list.size() == 0){
            return null;
        }
        return list.get(0);
    }

    //使用聚合函数COUNT查询个数
    public int executeCount(String sql, Object... obj) throws Exception {
        //1.获取连接
        Connection connection = JDBCUtil.getConnection();

        //2.预编译SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //3.为占位符取值,执行SQL,接受返回结果
        if(obj != null && obj.length > 0){
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i+1, obj[i]);
            }
        }

        //4.执行SQL 并接受返回的结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        resultSet.next();
        Long num = (Long) resultSet.getObject(1);

        connection.commit();
        resultSet.close();
        preparedStatement.close();
        if(!connection.getAutoCommit()){
            JDBCUtil.release();
        }
        return num.intValue();
    }

}
